package fu.capstone.hanoicityguide.dataaccess.nearbyplace;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.PointF;
import android.util.Log;
import fu.capstone.hanoicityguide.R;
import fu.capstone.hanoicityguide.business.model.Category;
import fu.capstone.hanoicityguide.business.model.GeoCoordinate;
import fu.capstone.hanoicityguide.business.model.Place;
import fu.capstone.hanoicityguide.common.utility.CreateDerivedPoint;
import fu.capstone.hanoicityguide.dataaccess.base.BaseDAOImpl;
import fu.capstone.hanoicityguide.dataaccess.base.DatabaseHelper;

public class NearbyPlaceDAOImp extends BaseDAOImpl implements NearbyPlaceDAO {

	private SQLiteDatabase database;
	private DatabaseHelper dbHelper;
	private Context context;
	private String strWhere;

	public NearbyPlaceDAOImp(Context ctx) {
		super(ctx);
		this.context = ctx;
	}

	/*
	 * currentLat, Long is the user's current location, radius of near by
	 * circle, in meter
	 */
	@Override
	public ArrayList<Place> listNearbyPlace(double currentLat,
			double currentLong, double radius, String keyword,
			ArrayList<String> filter, String sorter) {
		// TODO Auto-generated method stub

		Cursor cursor = null;
		ArrayList<Place> arrResult = new ArrayList<Place>();

		PointF center = new PointF((float) currentLat, (float) currentLong);
		final double mult = 1; // mult = 1.1; is more reliable
		PointF p1 = CreateDerivedPoint.calculateDerivedPosition(center, mult
				* radius, 0);
		PointF p2 = CreateDerivedPoint.calculateDerivedPosition(center, mult
				* radius, 90);
		PointF p3 = CreateDerivedPoint.calculateDerivedPosition(center, mult
				* radius, 180);
		PointF p4 = CreateDerivedPoint.calculateDerivedPosition(center, mult
				* radius, 270);

		strWhere = " WHERE " + "Geolocation.[Latitude]" + " > "
				+ String.valueOf(p3.x) + " AND " + "Geolocation.[Latitude]"
				+ " < " + String.valueOf(p1.x) + " AND "
				+ "Geolocation.[Longitude]" + " < " + String.valueOf(p2.y)
				+ " AND " + "Geolocation.[Longitude]" + " > "
				+ String.valueOf(p4.y);

		try {
			dbHelper = connectDB();
			database = dbHelper.getReadableDatabase();
			String queryNearby = "SELECT Place.[Id], Place.[Name], Place.[TypeId], Type.[TypeName], Place.[GeoId], Category.[CategoryName] , "
					+ "Geolocation.[Latitude], Geolocation.[Longitude] , AlbumImage.[IconPath] , Rating.[RatingValue]  FROM Place "
					+ "LEFT JOIN Type ON Place.[TypeID] = Type.[Id] "
					+ "LEFT JOIN Category ON Place.[CategoryId] = Category.[Id] "
					+ "LEFT JOIN AlbumImage ON Place.[AlbumImageId] = AlbumImage.[Id] "
					+ "LEFT JOIN Rating ON Place.[RatingId] = Rating.[Id] "
					+ "LEFT JOIN Geolocation ON Place.[GeoId] = Geolocation.[Id] "
					+ strWhere;

			// filter by keyword
			queryNearby = queryNearby + " AND Place.[Name] LIKE '%" + keyword
					+ "%' ";

			// filter
			if (filter.size() > 0) {
				queryNearby = queryNearby
						+ " AND ( Category.[CategoryName] == \""
						+ filter.get(0) + "\"";
				for (int i = 1; i < filter.size(); i++) {
					queryNearby = queryNearby
							+ " OR Category.[CategoryName] == \""
							+ filter.get(i) + "\"";
				}
				queryNearby = queryNearby + " ) ";
			} else {
				queryNearby = queryNearby
						+ " AND Category.[CategoryName] == \"\"";
			}

			// sort
			if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[0])) {
				queryNearby = queryNearby
						+ "ORDER BY Rating.[RatingValue] DESC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[1])) {
				queryNearby = queryNearby + "ORDER BY Rating.[RatingValue] ASC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[2])) {
				queryNearby = queryNearby + "ORDER BY Place.[Name] DESC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[3])) {
				queryNearby = queryNearby + "ORDER BY Place.[Name] ASC";
			} else {
			}

			cursor = database.rawQuery(queryNearby, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					do {
						Place place = new Place();
						place.setId(cursor.getInt(cursor.getColumnIndex("Id")));
						place.setName(cursor.getString(cursor
								.getColumnIndex("Name")));
						place.setType(cursor.getString(cursor
								.getColumnIndex("TypeName")));
						place.setQuality(cursor.getInt(cursor
								.getColumnIndex("RatingValue")));
						place.setIconImage((cursor.getString(cursor
								.getColumnIndex("IconPath"))));
						place.setCategory(cursor.getString(cursor
								.getColumnIndex("CategoryName")));
						GeoCoordinate geo = new GeoCoordinate();
						geo.setLatitude(Double.parseDouble(cursor
								.getString(cursor.getColumnIndex("Latitude"))));
						geo.setLongitude(Double.parseDouble(cursor
								.getString(cursor.getColumnIndex("Longitude"))));
						place.setGeo(geo);
						// Log.v("RatingValue : ", place.getQuality() + "");
						arrResult.add(place);
					} while (cursor.moveToNext());
				}
			}
		} catch (Exception e) {
			Log.v("Can't query DB", e.toString());
		} finally {
			closeDB(database, dbHelper);
		}

		return arrResult;
	}

	@Override
	public ArrayList<Category> getListCategory(String filter) {
		// TODO Auto-generated method stub
		return super.getListCategory(filter);
	}
}
